Imports eyeLog.Tools

Namespace Core
  Public Class Kernel

#Region "Events"
    Public Event OnNotify(ByVal sender As Object, ByVal e As NotifyEventArgs)
    Public Event OnPreProcess(ByVal sender As Object, ByVal e As ProgressEventArgs)
    Public Event OnProcess(ByVal sender As Object, ByVal e As ProgressEventArgs)
    Public Event OnPostProcess(ByVal sender As Object, ByVal e As ProgressEventArgs)
#End Region

#Region "Private Members"
    Private _edition As Editions = Editions.Personal
    Private _csb As New SqlConnectionStringBuilder
    Private mVersion As SqlVersion
    Private mDatabases As New DataTable
    Private mDatabase As Database
    Private mTables As New List(Of Table)
    Private mUseSchema As Boolean
    Private mSchemaFound As Boolean
#End Region

#Region "Properties"
    ReadOnly Property Edition() As Editions
      Get
        Return _edition
      End Get
    End Property

    ReadOnly Property DataSource() As String
      Get
        Return _csb.DataSource
      End Get
    End Property

    ReadOnly Property InitialCatalog() As String
      Get
        Return _csb.InitialCatalog
      End Get
    End Property

    ReadOnly Property Version() As SqlVersion
      Get
        Return mVersion
      End Get
    End Property

    ReadOnly Property Database() As Database
      Get
        Return mDatabase
      End Get
    End Property

    ReadOnly Property Tables() As List(Of Table)
      Get
        Return mTables
      End Get
    End Property

    ReadOnly Property ConnectionString() As String
      Get
        Return _csb.ConnectionString
      End Get
    End Property

    ReadOnly Property Databases() As DataTable
      Get
        Return mDatabases
      End Get
    End Property

    Property UseSchema() As Boolean
      Get
        Return mUseSchema
      End Get
      Set(ByVal value As Boolean)
        If value = True Then
          Select Case mVersion
            Case SqlVersion.Sql2000
              Throw New ArgumentException("eyeLog does not support schemas on this version of SQL Server")
            Case SqlVersion.Sql2005
              If _edition = Editions.Personal Then Throw New ArgumentException("eyeLog Personal Edition does not support schemas")
          End Select
        End If
        mUseSchema = value
      End Set
    End Property

    ReadOnly Property NewStatus() As TableStatuses
      Get
        Dim item As Table
        Dim rc As TableStatuses

        For Each item In mTables
          rc = rc Or item.NewStatus
        Next

        Return rc
      End Get
    End Property
#End Region

#Region "Methods"
    Public Shared Function GetVersionInfo() As String
      Dim rc As New System.Text.StringBuilder

      With Reflection.Assembly.GetExecutingAssembly().GetName().Version
        'rc.AppendLine(String.Format("Core Engine Version : {0}.{1:D2} (Build {2:D1} rev. {3:D3})", .Major, .Minor, .Build, .Revision))
        rc.AppendLine(String.Format("Core Engine Version : {0}.{1:D2} (Build {2:D3})", .Major, .Minor, .Build, .Revision))
      End With
      With My.Application.Info
        'rc.AppendLine(String.Format("Application Version : {0}.{1:D2} (Build {2:D1} rev. {3:D3})", .Version.Major, .Version.Minor, .Version.Build, .Version.Revision))
        rc.AppendLine(String.Format("Application Version : {0}.{1:D2} (Build {2:D3})", .Version.Major, .Version.Minor, .Version.Build, .Version.Revision))
      End With

      Return rc.ToString
    End Function

    Public Sub Register(ByVal applicationCode As Byte, ByVal licenseKey As String)
      Dim l As New LicenseBase(applicationCode, licenseKey)
      If l.ApplicationCode = applicationCode Then
        _edition = applicationCode
#If DEBUG Then
      Else
        Stop
#End If
      End If
    End Sub

    Public Sub SetServer(ByVal dataSource As String, ByVal integratedSecurity As Boolean, ByVal userId As String, ByVal password As String)
      Dim dr As SqlDataReader
      Dim cn As SqlConnection
      Dim cmd As SqlCommand

      _csb.ApplicationName = My.Application.Info.Title
      _csb.WorkstationID = "WKS"

      If dataSource IsNot Nothing Then dataSource = dataSource.ToLower()
      Select Case _edition
        Case Editions.Personal
          If dataSource IsNot Nothing AndAlso dataSource <> "(local)" AndAlso dataSource <> "." Then
            dataSource = "(local)"
            MsgBox("Only the default instance on the local SQL Server" & vbCrLf & "is supported by eyeLog Lite Edition.", MsgBoxStyle.Information)
          End If
        Case Editions.IPKG
          If dataSource Is Nothing OrElse dataSource.StartsWith("ipbe") = False Then
            MsgBox("eyeLog IPKG Edition only supports IPBExxxxx servers.", MsgBoxStyle.Information)
            Exit Sub
          End If
      End Select

      ' === Initialize the ConnectionStringBuilder object ====================
      RaiseEvent OnNotify(Me, New NotifyEventArgs("Setting server information"))
      With _csb
        .DataSource = dataSource
        .IntegratedSecurity = integratedSecurity
        .UserID = IIf(integratedSecurity = True, "", userId)
        .Password = IIf(integratedSecurity = True, "", password)
        ' --- Force the default database -------------------------------------
        .InitialCatalog = "master"
      End With

      cn = Connection(_csb.ConnectionString)
      cmd = cn.CreateCommand

      ' === Verify the version of SQL Server =================================
      ' The version is important on local data source in order to determine the
      ' schema of the tables
      RaiseEvent OnNotify(Me, New NotifyEventArgs("Retrieving server's version"))
      cmd.CommandText = "EXEC xp_msver ProductVersion"
      dr = cmd.ExecuteReader
      If dr.Read() = True Then
        Select Case dr("Character_Value").ToString().Substring(0, 1)
          Case "8"
            mVersion = SqlVersion.Sql2000
            mUseSchema = False
          Case "9"
            mVersion = SqlVersion.Sql2005
            mUseSchema = IIf(_edition = Editions.Personal, False, True)
          Case Else
            Throw New InvalidOperationException(String.Format("The version of the local Data Source [{0}] is not supported by eyeLog.", _csb.DataSource))
        End Select
      Else
        Throw New InvalidOperationException(String.Format("The version of the local Data Source [{0}] could not be found.", _csb.DataSource))
      End If

      cmd.Connection = Nothing
      cn.Close()

      GetDatabases()
    End Sub

    Function GetDatabases() As DataTable
      Dim da As SqlDataAdapter

      ' === Force the initial catalog ========================================
      _csb.InitialCatalog = "master"
      mDatabases.Clear()

      RaiseEvent OnNotify(Me, New NotifyEventArgs("Retrieving databases"))
      da = New SqlDataAdapter("SELECT Name FROM sysDatabases WHERE HAS_DBACCESS(Name)=1 AND Name NOT IN ('model','master','tempdb','msdb') ORDER BY Name", _csb.ConnectionString)
      da.Fill(mDatabases)
      da = Nothing

      Return mDatabases
    End Function

    Sub SetDatabase(ByVal initialCatalog As String)
      Dim query As String
      Dim cn As SqlConnection
      Dim da As SqlDataAdapter
      Dim dt As New DataTable
      Dim dr As DataRow
      Dim i As Integer
      Dim cmd As SqlCommand
      Dim item As Table

      ' === Set the Initial Catalog ==========================================
      _csb.InitialCatalog = initialCatalog
      cn = Connection(_csb.ConnectionString)

      ' === Find the schema ==================================================
      If mVersion = SqlVersion.Sql2005 Then
        cmd = cn.CreateCommand
        cmd.CommandText = String.Format("SELECT ISNULL(SCHEMA_ID('{0}'),0)", PFX_SCHEMA)
        mSchemaFound = (cmd.ExecuteScalar <> 0)
      End If

      ' === Retrieve the Database-level audit policy =========================
      If mVersion = SqlVersion.Sql2005 Then
        query = String.Format("SELECT {0} AS tblSchema, name AS tblName, NULL AS tblSchemaHistory, NULL AS tblNameHistory " _
                        & "FROM sysObjects O " _
                        & "WHERE xtype='U' " _
                        & "AND name LIKE '%{1}{2}'" _
                        & "ORDER BY tblSchema,tblName", _
                          IIf(mVersion = SqlVersion.Sql2005, "SCHEMA_NAME(uid)", "USER_NAME(uid)"), _
                          _csb.InitialCatalog, _
                          SFX_TRIGGER_DDL)
        da = New SqlDataAdapter(query, cn)
        da.Fill(dt)
        If dt.Rows.Count > 0 Then
          mDatabase = New Database(Me, dt.Rows(0))
        Else
          mDatabase = New Database(Me, Nothing)
        End If
      Else
        ' DDL Triggers do not exist on SQL Server 2000
        mDatabase = Nothing
      End If

      ' === Retrieve the tables of the database ==============================
      mTables.Clear()
      query = String.Format("SELECT {0} AS tblSchema, name AS tblName " _
                          & ",(SELECT {2}_NAME(uid) FROM sysObjects WHERE xtype='U' AND ((name LIKE '{1}_' + O.name) OR ({2}_NAME(uid)='{1}' AND name = O.name))) AS tblSchemaHistory " _
                          & ",(SELECT name FROM sysObjects WHERE xtype='U' AND ((name LIKE '{1}_' + O.name) OR ({2}_NAME(uid)='{1}' AND name = O.name))) AS tblNameHistory " _
                          & "FROM sysObjects O " _
                          & "WHERE xtype='U' " _
                          & "AND (name NOT LIKE '{1}%') " _
                          & "AND ({2}_NAME(uid) NOT LIKE '{1}') " _
                          & "ORDER BY tblSchema,tblName", _
                            IIf(mVersion = SqlVersion.Sql2005, "SCHEMA_NAME(uid)", "USER_NAME(uid)"), _
                            PFX_SCHEMA, _
                            IIf(mVersion = SqlVersion.Sql2005, "SCHEMA", "USER"))
      da = New SqlDataAdapter(query, cn)
      da.Fill(dt)
      RaiseEvent OnPreProcess(Me, New ProgressEventArgs(dt.Rows.Count, "Retrieving tables information"))
      For Each dr In dt.Rows
        i += 1
        RaiseEvent OnProcess(Me, New ProgressEventArgs(i, String.Format("Retrieving table structure for table '{0}'", dr("tblName"))))
        If dr("tblName").ToString().StartsWith(PFX_SCHEMA) = False Then
          item = New Table(Me, dr)
          mTables.Add(item)
        End If
      Next
      RaiseEvent OnPostProcess(Me, New ProgressEventArgs(dt.Rows.Count, "Tables information retrieved"))

      cn.Close()
    End Sub

    Sub SetDatabase(ByVal filename As String, ByVal logicalName As String)
      _csb.AttachDBFilename = filename
      _csb.InitialCatalog = logicalName
    End Sub

    Protected Friend Sub TriggerOnNotify(ByVal sender As Object, ByVal e As NotifyEventArgs)
      RaiseEvent OnNotify(sender, e)
    End Sub
    Protected Friend Sub TriggerOnPreProcess(ByVal sender As Object, ByVal e As ProgressEventArgs)
      RaiseEvent OnPreProcess(sender, e)
    End Sub
    Protected Friend Sub TriggerOnProcess(ByVal sender As Object, ByVal e As ProgressEventArgs)
      RaiseEvent OnProcess(sender, e)
    End Sub
    Protected Friend Sub TriggerOnPostProcess(ByVal sender As Object, ByVal e As ProgressEventArgs)
      RaiseEvent OnPostProcess(sender, e)
    End Sub

    Function GetSummary() As String
      Dim rc As New System.Text.StringBuilder
      Dim item As Table
      Dim events As List(Of String)
      Dim parser As EnumTree

      rc.AppendFormat("{0}eyeLog will run with the following parameters:", vbCrLf)
      rc.AppendFormat("{0}==============================================", vbCrLf)
      If _csb.AttachDBFilename IsNot Nothing AndAlso _csb.AttachDBFilename.Length > 0 Then
        rc.AppendFormat("{0}- Filename                : {1} on {2} as {3}", vbCrLf, _csb.AttachDBFilename, _csb.DataSource, _csb.InitialCatalog)
      Else
        rc.AppendFormat("{0}- Database                : {1}@{2}", vbCrLf, _csb.InitialCatalog, _csb.DataSource)
      End If
      rc.AppendFormat("{0}- Version of MS-SQL Server: {1}", vbCrLf, CInt(mVersion))
      rc.AppendFormat("{0}- Logging Isolation Mode  : {1}", vbCrLf, IIf(mUseSchema = True, "use schemas", "use prefixes"))
      rc.AppendFormat("{0}", vbCrLf)
      rc.AppendFormat("{0}Database-level logging strategy:", vbCrLf)
      rc.AppendFormat("{0}--------------------------------", vbCrLf)
      rc.AppendFormat("{0}   - Strategy: {1}", vbCrLf, IIf(mDatabase.OldStatus = mDatabase.NewStatus, String.Format("Leave ({0})", mDatabase.OldStatus), mDatabase.NewStatus))
      If (mDatabase.OldStatus <> mDatabase.NewStatus) OrElse (mDatabase.OldStatus = DatabaseStatuses.Installed) Then
        rc.AppendFormat("{0}   - Event table: {1}", vbCrLf, mDatabase.Table)
        rc.AppendFormat("{0}   - Trigger events: ", vbCrLf)
        parser = New EnumTree(GetType(DDL_EVENTS))
        events = parser.ParseValue(mDatabase.Events)
        For Each v As String In events
          rc.AppendFormat("{0}      > {1} ", vbCrLf, v)
        Next
      End If
      rc.AppendFormat("{0}", vbCrLf)
      rc.AppendFormat("{0}Table-level logging strategy:", vbCrLf)
      rc.AppendFormat("{0}-----------------------------", vbCrLf)
      For Each item In mTables
        rc.AppendFormat("{0}   - [{1}].[{2}]: {3}", vbCrLf, item.Schema, item.Name, item.NewStatus)
      Next
      Return rc.ToString()
    End Function

    Private Shared Sub AddCommentLine(ByVal script As List(Of String), ByVal line As String)
      Dim i As Integer
      Dim chunk As Integer = 73

      For i = 0 To line.Length Step chunk
        script.Add(String.Format(" * {0}{1} *", _
                                 line.Substring(i, IIf(chunk + i > line.Length, line.Length Mod chunk, chunk)), _
                                 Space(73 - IIf(chunk + i > line.Length, line.Length Mod chunk, chunk))))
      Next
    End Sub

    Public Function GetScript() As String
      Dim rc As New System.Text.StringBuilder
      Dim script As New List(Of String)
      Dim line As String
      Dim item As Table
      Dim events As List(Of String)
      Dim parser As EnumTree

      script.Add("/*****************************************************************************")
      AddCommentLine(script, String.Format("{0} {1} Edition", My.Application.Info.Title, _edition))
      AddCommentLine(script, My.Application.Info.Description)
      AddCommentLine(script, New String("-", 73))

      AddCommentLine(script, String.Format("- Script date     : {0}", Now))
      If _csb.AttachDBFilename IsNot Nothing AndAlso _csb.AttachDBFilename.Length > 0 Then
        AddCommentLine(script, String.Format("- Filename        : {1} on {2} as {3}", vbCrLf, _csb.AttachDBFilename, _csb.DataSource, _csb.InitialCatalog))
      Else
        AddCommentLine(script, String.Format("- Database        : {1}@{2}", vbCrLf, _csb.InitialCatalog, _csb.DataSource))
      End If
      AddCommentLine(script, String.Format("- Server Version  : {1}", vbCrLf, CInt(mVersion)))
      AddCommentLine(script, String.Format("- Isolation Mode  : {1}", vbCrLf, IIf(mUseSchema = True, "use schemas", "use prefixes")))
      AddCommentLine(script, String.Format("Database-level logging strategy:", vbCrLf))
      AddCommentLine(script, String.Format("--------------------------------", vbCrLf))
      AddCommentLine(script, String.Format("   - Strategy: {1}", vbCrLf, IIf(mDatabase.OldStatus = mDatabase.NewStatus, String.Format("Leave ({0})", mDatabase.OldStatus), mDatabase.NewStatus)))
      If (mDatabase.OldStatus <> mDatabase.NewStatus) OrElse (mDatabase.OldStatus = DatabaseStatuses.Installed) Then
        AddCommentLine(script, String.Format("   - Event table: {1}", vbCrLf, mDatabase.Table))
        AddCommentLine(script, String.Format("   - Trigger events: ", vbCrLf))
        parser = New EnumTree(GetType(DDL_EVENTS))
        events = parser.ParseValue(mDatabase.Events)
        For Each v As String In events
          AddCommentLine(script, String.Format("      > {1} ", vbCrLf, v))
        Next
      End If

      AddCommentLine(script, String.Format("- Table-level logging strategy:", vbCrLf))
      For Each item In mTables
        AddCommentLine(script, String.Format("   - [{1}].[{2}]: {3}", vbCrLf, item.Schema, item.Name, item.NewStatus))
      Next
      AddCommentLine(script, New String("-", 73))

      With Reflection.Assembly.GetExecutingAssembly().GetName().Version
        AddCommentLine(script, String.Format("Core Engine Version : {0}.{1:D2} (Build {2:D1} rev. {3:D3})", .Major, .Minor, .Build, .Revision))
      End With
      With My.Application.Info
        AddCommentLine(script, String.Format("Application Version : {0}.{1:D2} (Build {2:D1} rev. {3:D3})", .Version.Major, .Version.Minor, .Version.Build, .Version.Revision))
        If (_edition And (Editions.Personal Or Editions.Professional Or Editions.Standard)) <> 0 Then
          AddCommentLine(script, String.Format("Author              : {0}", .CompanyName))
          AddCommentLine(script, String.Format("Copyright           : {0}", .Copyright))
        End If
      End With
      script.Add(" *****************************************************************************/")

      AddScriptItem(script, String.Format("USE {1};", vbCrLf, _csb.InitialCatalog))
      AddScriptItem(script, String.Format("GO{0}", vbCrLf))

      If (mVersion = SqlVersion.Sql2005) AndAlso (mSchemaFound = False) AndAlso (((NewStatus And TableStatuses.Standard) = TableStatuses.Standard) OrElse (mDatabase.NewStatus = DatabaseStatuses.Installed)) Then
        AddScriptItem(script, String.Format("CREATE SCHEMA {1};", vbCrLf, PFX_SCHEMA))
        AddScriptItem(script, String.Format("GO{0}", vbCrLf))
      End If

      GenerateScript(script)

      If mVersion AndAlso mSchemaFound = True AndAlso (NewStatus And TableStatuses.Standard) <> TableStatuses.Standard Then
        AddScriptItem(script, String.Format("IF EXISTS (SELECT * FROM sys.schemas WHERE schema_id = SCHEMA_ID('{1}'))", vbCrLf, PFX_SCHEMA))
        AddScriptItem(script, String.Format("DROP SCHEMA {1};", vbCrLf, PFX_SCHEMA))
        AddScriptItem(script, String.Format("GO{0}", vbCrLf))
      End If

      For Each line In script
        rc.AppendLine(line)
      Next

      Return rc.ToString()
    End Function

    Private Sub GenerateScript(ByVal script As List(Of String))
      'Dim script As New List(Of String)
      Dim rc As New System.Text.StringBuilder
      Dim t As Table
      Dim s As String
      Dim i As Integer

      '=== Database-level logging ============================================
      RaiseEvent OnPreProcess(Me, New ProgressEventArgs(1, "Scripting database-level logging"))
      RaiseEvent OnProcess(Me, New ProgressEventArgs(i, "Scripting changes on database"))
      mDatabase.GetScript(script)
      RaiseEvent OnPostProcess(Me, New ProgressEventArgs("Database-level changes scripted"))

      ' === Table-level logging ==============================================
      RaiseEvent OnPreProcess(Me, New ProgressEventArgs(mTables.Count, "Scripting table-level logging"))

      For Each t In mTables
        i += 1
        RaiseEvent OnProcess(Me, New ProgressEventArgs(i, String.Format("Scripting changes on table [{0}].[{1}]", t.Schema, t.Name)))
        t.GetScript(script)
      Next

      RaiseEvent OnPostProcess(Me, New ProgressEventArgs("Table-level changes scripted"))

      For Each s In script
        rc.AppendLine(s)
      Next
    End Sub

    Sub RunScript()
      Dim cn As SqlConnection
      Dim cmd As SqlCommand
      Dim trn As SqlTransaction
      'Dim rc As New System.Text.StringBuilder
      Dim script As New List(Of String)
      Dim line As String
      Dim i As Integer

      RaiseEvent OnNotify(Me, New NotifyEventArgs("Generating script to be applied"))
      GenerateScript(script)

      RaiseEvent OnPreProcess(Me, New ProgressEventArgs(script.Count, "Applying script"))
      cn = New SqlConnection(_csb.ConnectionString)
      cn.Open()

      trn = cn.BeginTransaction
      cmd = cn.CreateCommand
      cmd.Transaction = trn

      Try
        If mVersion = SqlVersion.Sql2005 AndAlso mSchemaFound = False AndAlso ((NewStatus And TableStatuses.Standard) = TableStatuses.Standard) OrElse (mDatabase.NewStatus = DatabaseStatuses.Installed) Then
          cmd.CommandText = String.Format("{0}CREATE SCHEMA {1};", vbCrLf, PFX_SCHEMA)
          cmd.ExecuteNonQuery()
        End If

        For Each line In script
          i += 1
          RaiseEvent OnProcess(Me, New ProgressEventArgs(i, String.Format("Applying script statement #{0}", i)))
          If line IsNot Nothing AndAlso line <> "GO" Then
            cmd.CommandText = line
            cmd.ExecuteNonQuery()
          End If
        Next

        RaiseEvent OnPostProcess(Me, New ProgressEventArgs("Finalizing script"))
        If mVersion AndAlso mSchemaFound = True AndAlso (NewStatus And TableStatuses.Standard) <> TableStatuses.Standard Then
          cmd.CommandText = String.Format("{0}IF EXISTS (SELECT * FROM sys.schemas WHERE schema_id = SCHEMA_ID('{1}')) DROP SCHEMA {1}", vbCrLf, PFX_SCHEMA)
          cmd.ExecuteNonQuery()
        End If

#If DEBUG Then
        If MsgBox("The script has been successfully executed. Would you like to commit the changes?", MsgBoxStyle.Question Or MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
#End If
          trn.Commit()
          RaiseEvent OnNotify(Me, New NotifyEventArgs("Script successfully committed"))
#If DEBUG Then
        Else
          trn.Rollback()
          RaiseEvent OnNotify(Me, New NotifyEventArgs("Script successfully rolled back"))
        End If
#End If
      Catch ex As Exception
        MsgBox(ex.ToString(), MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "eyeLog")
#If DEBUG Then
        Stop
#End If
        RaiseEvent OnNotify(Me, New NotifyEventArgs("Script has not been committed"))
        trn.Rollback()
      End Try
    End Sub
#End Region
  End Class
End Namespace